package com.gesis.persistence.imp;

import java.util.List;
import java.util.logging.Level;

import javax.persistence.EntityManager;
import javax.persistence.Query;

import com.gesis.entity.GesSubClassification;
import com.gesis.entity.GesTopic;
import com.gesis.persistence.EntityManagerHelper;

public class CustomQuery {

	private EntityManager getEntityManager() {
		return EntityManagerHelper.getEntityManager();
	}

	@SuppressWarnings("unchecked")
	public List<GesTopic> searchForYear(String yearBegin, String yearEnd)
			throws Exception {
		EntityManagerHelper.log("finding GesTopic instance with value: "
				+ yearBegin + " and " + yearEnd, Level.INFO, null);
		try {
			final String queryString = "select top from GesTopic top, GesClassification cla, GesSubClassification sub " +
					"where cla.claIdClassification = sub.gesClassification.claIdClassification and " +
					"sub.subIdTopic = top.gesSubClassification.subIdTopic and " +
					"((top.topStartDate between :yearBegin and :yearEnd) or " +
					"(top.topEndDate between :yearBegin and :yearEnd))";
			Query query = getEntityManager().createQuery(queryString);
			query.setParameter("yearBegin", new Integer(yearBegin));
			query.setParameter("yearEnd", new Integer(yearEnd));
			return query.getResultList();
		} catch (RuntimeException re) {
			EntityManagerHelper.log("find by property name failed",
					Level.SEVERE, re);
			throw re;
		}
	}
	
	@SuppressWarnings("unchecked")
	public List<GesSubClassification> searchClassification(String clas)throws Exception{
		try {
			final String queryString = "select sub from GesSubClassification sub where " +
					"sub.gesClassification.claIdClassification = :clas";
			Query query = getEntityManager().createQuery(queryString);
			query.setParameter("clas", new Long(clas));
			return query.getResultList();
		} catch (Exception e) {
			throw e;
		}
	}
	
	@SuppressWarnings("unchecked")
	public List<GesTopic> findSubClassification(String yearBegin, String yearEnd, String idCla)throws Exception{
		try {
			final String queryString = "select distinct(top.gesSubClassification.subIdTopic) from GesTopic top, " +
					"GesClassification cla, GesSubClassification sub " +
					"where cla.claIdClassification = sub.gesClassification.claIdClassification and " +
					"sub.subIdTopic = top.gesSubClassification.subIdTopic and " +
					"((top.topStartDate between :yearBegin and :yearEnd) or " +
					"(top.topEndDate between :yearBegin and :yearEnd)) and cla.claIdClassification = :idCla";
			Query query = getEntityManager().createQuery(queryString);
			query.setParameter("yearBegin", new Integer(yearBegin));
			query.setParameter("yearEnd", new Integer(yearEnd));
			query.setParameter("idCla", new Long(idCla));
			return query.getResultList();
		} catch (RuntimeException re) {
			EntityManagerHelper.log("find by property name failed",
					Level.SEVERE, re);
			throw re;
		}
	}
	
	public int countTopic(String yearBegin, String yearEnd, String idCla, String idTopic)throws Exception{
		try {
			final String queryString = "select count(top.gesSubClassification.subIdTopic) from GesTopic top, " +
					"GesClassification cla, GesSubClassification sub " +
					"where cla.claIdClassification = sub.gesClassification.claIdClassification and " +
					"sub.subIdTopic = top.gesSubClassification.subIdTopic and " +
					"((top.topStartDate between :yearBegin and :yearEnd) or " +
					"(top.topEndDate between :yearBegin and :yearEnd)) and cla.claIdClassification = :idCla and " +
					"top.gesSubClassification.subIdTopic = :idTopic";
			Query query = getEntityManager().createQuery(queryString);
			query.setParameter("yearBegin", new Integer(yearBegin));
			query.setParameter("yearEnd", new Integer(yearEnd));
			query.setParameter("idCla", new Long(idCla));
			query.setParameter("idTopic", new Long(idTopic));
			Long count = (Long)query.getSingleResult();
			String strCount = count.toString();
			return Integer.parseInt(strCount);
		} catch (RuntimeException re) {
			EntityManagerHelper.log("find by property name failed",
					Level.SEVERE, re);
			throw re;
		}
	}
}
